﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Catering_DBOpertion;
using System.Data;//Please add references

namespace Catering_Operation
{
    public class ContactsDAL
    {
        public int Del(string id)
        {
            string sql = "DELETE FROM [CMS_Catering].[dbo].[ContactsInfo] WHERE id = @id";
            SqlParameter[] parm = new SqlParameter[] {
                new SqlParameter("@id",id)
            };
            return DbHelperSQL.ExecuteSql(sql, parm);
        }

        public int Update(string areaid, string classid, string name, string sex, string jobtitle, string phonenum, string back, string temp1, string temp2, string id)
        {
            string sql = @"UPDATE [CMS_Catering].[dbo].[ContactsInfo]
   SET [areaId] = @a
      ,[classId] = @c
      ,[name] = @n
      ,[sex] = @s
      ,[jobTitle] = @j
      ,[phoneNum] = @p
      ,[back] = @b
      ,[temp1] = @t1
      ,[temp2] = @t2
 WHERE id=@id";
            SqlParameter[] parm = new SqlParameter[] {
                new SqlParameter("@a",areaid),
                new SqlParameter("@c",classid),
                new SqlParameter("@n",name),
                new SqlParameter("@s",sex),
                new SqlParameter("@j",jobtitle),
                new SqlParameter("@p",phonenum),
                new SqlParameter("@b",back),
                new SqlParameter("@t1",temp1),
                new SqlParameter("@t2",temp2),
                new SqlParameter("@id",id)
            };
            return DbHelperSQL.ExecuteSql(sql, parm);
        }

        public int Add(string areaid, string classid, string name, string sex, string jobtitle, string phonenum, string back, string temp1, string temp2)
        {
            int num = 0;
            string sql = @"INSERT INTO [CMS_Catering].[dbo].[ContactsInfo]
           ([areaId]
           ,[classId]
           ,[name]
           ,[sex]
           ,[jobTitle]
           ,[phoneNum]
           ,[back]
           ,[temp1]
           ,[temp2])
     VALUES
           (@a,@c,@n,@s,@j,@p,@b,@t1,@t2)";
            SqlParameter[] parm = new SqlParameter[] {
                new SqlParameter("@a",areaid),
                new SqlParameter("@c",classid),
                new SqlParameter("@n",name),
                new SqlParameter("@s",sex),
                new SqlParameter("@j",jobtitle),
                new SqlParameter("@p",phonenum),
                new SqlParameter("@b",back),
                new SqlParameter("@t1",temp1),
                new SqlParameter("@t2",temp2)
            };
            num = DbHelperSQL.ExecuteSql(sql, parm);
            return num;
        }

        public DataTable GetContactsForArea(string id)
        {
            string sql = "";
            if (string.IsNullOrEmpty(id))
            {
                sql = @"SELECT     dbo.ClassInfo.CName, dbo.OperationArea.AreaName, dbo.ContactsInfo.id, dbo.ContactsInfo.areaId, dbo.ContactsInfo.classId, dbo.ContactsInfo.name, 
                      dbo.ContactsInfo.sex, dbo.ContactsInfo.jobTitle, dbo.ContactsInfo.phoneNum, dbo.ContactsInfo.back, dbo.ContactsInfo.temp1, 
                      dbo.ContactsInfo.temp2
FROM         dbo.ClassInfo INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id INNER JOIN
                      dbo.ContactsInfo ON dbo.ClassInfo.id = dbo.ContactsInfo.classId";
                return DbHelperSQL.Query(sql).Tables[0];
            }
            else
            {
                sql = @"SELECT     dbo.ClassInfo.CName, dbo.OperationArea.AreaName, dbo.ContactsInfo.id, dbo.ContactsInfo.areaId, dbo.ContactsInfo.classId, dbo.ContactsInfo.name, 
                      dbo.ContactsInfo.sex, dbo.ContactsInfo.jobTitle, dbo.ContactsInfo.phoneNum, dbo.ContactsInfo.back, dbo.ContactsInfo.temp1, 
                      dbo.ContactsInfo.temp2
FROM         dbo.ClassInfo INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id INNER JOIN
                      dbo.ContactsInfo ON dbo.ClassInfo.id = dbo.ContactsInfo.classId where dbo.ContactsInfo.areaId = @id";
                SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",id)
            };
                return DbHelperSQL.Query(sql, parm).Tables[0];
            }
        }

        /// <summary>
        /// 获取单个用户信息
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns></returns>
        public DataTable GetContactsForUserId(string id)
        {
            string sql = "";
            sql = @"SELECT     dbo.ClassInfo.CName, dbo.OperationArea.AreaName, dbo.ContactsInfo.id, dbo.ContactsInfo.areaId, dbo.ContactsInfo.classId, dbo.ContactsInfo.name, 
                      dbo.ContactsInfo.sex, dbo.ContactsInfo.jobTitle, dbo.ContactsInfo.phoneNum, dbo.ContactsInfo.back, dbo.ContactsInfo.temp1, 
                      dbo.ContactsInfo.temp2
FROM         dbo.ClassInfo INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id INNER JOIN
                      dbo.ContactsInfo ON dbo.ClassInfo.id = dbo.ContactsInfo.classId where dbo.ContactsInfo.id = @id";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",id)
            };
            return DbHelperSQL.Query(sql, parm).Tables[0];

        }
    }
}
